package com.zhiletu.collect;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.zhiletu.collect.entry.ArticleEntry;
import com.zhiletu.collect.entry.CollecterEntry;
import com.zhiletu.collect.util.ChineseCharacterUtil;
import com.zhiletu.collect.util.DBUtil;
import com.zhiletu.collect.util.DateUtil;
import com.zhiletu.collect.util.ParseString;
import com.zhiletu.collect.util.Random;
import com.zhiletu.common.SingleID;
import org.apache.log4j.Logger;

/**
 *
 * @Title: 采集数据保存
 * @Package com.zhiletu.collect
 * @Description: 采集数据保存到wordpress数据库中
 * @author zhiletu.com
 * @date 2021年3月11日
 * @version V1.0
 */
public class CollectSave {
	private static Logger log = Logger.getLogger(CollectSave.class);

	private static int count = 0; // 操作完毕记得计数器清零


	/**
	 * @desc 批量新增入库
	 * @return 適配wordpress
	 */
	public static int save2DB(List<Map<String, String>> artlist, Map<String, Object> collecter, String catId) throws Exception {
		if (artlist.size() <= 0) {
			log.info("要保存的文章条数为0");
			return 0;
		}
		log.info("开始执行数据保存……");
		int[] users = { 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13 }; // 随机分配作者
		String tablePrefix = ParseString.nvl(collecter.get(CollecterEntry.tablePrefix));
		String insert = "INSERT INTO `" + tablePrefix + "_posts` (`id`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`) VALUES"
				+ "(NULL, ?, ?, ?, ?, ?, '', 'publish', 'open', 'closed', '', ?, '', '', ?, ?, '', '0', '', '0', 'post', '', '0')";

		List<Object[]> parArray = new ArrayList<Object[]>();
		List<Object[]> parArrayCat = new ArrayList<Object[]>();
		List<Object[][]> parArrayTag = new ArrayList<Object[][]>();

		String url = ParseString.nvl(collecter.get(CollecterEntry.dburl)); // 採集器目標數據庫  
		String username = ParseString.nvl(collecter.get(CollecterEntry.dbuser));
		String passwd = ParseString.nvl(collecter.get(CollecterEntry.dbpass));

		// 关联分类目录		
		int curMaxId = queryMaxId(url, username, passwd, tablePrefix);

		for (Map<String, String> art : artlist) {
			int rand = 0;
			try {
				rand = (int) Random.getRand(1);
				rand = rand >= users.length ? 0 : rand;
			}
			catch (Exception e) {
				rand = 2;
			}
			int user = users[rand];
			String insertTimestamp = DateUtil.getDateTimeStamp(count++);
			String title = art.get(ArticleEntry.articleTitle);
			String content = art.get(ArticleEntry.articleContent);
			String name = new SingleID().toString(); // 別名

			parArray.add(new Object[] { user, insertTimestamp, insertTimestamp, content, title, name, insertTimestamp, insertTimestamp });

			int postId = ++curMaxId;

			parArrayCat.add(new Object[] { postId, catId });

			String tags = art.get(ArticleEntry.articleTag);

			if (!ParseString.isNull(tags)) {
				String[] tagArr = tags.split(",");
				// 每个标签一个term对象
				Object[][] tagTerms = new Object[tagArr.length][3];

				for (int x = 0; x < tagArr.length; x++) {

					tagTerms[x][0] = tagArr[x]; // 标签名称
					tagTerms[x][1] = ChineseCharacterUtil.convertHanzi2Pinyin(tagArr[x], true); // 标签别名
					tagTerms[x][2] = postId; // 关联的帖子id
				}

				parArrayTag.add(tagTerms);
			}
		}

		String insertCat = "INSERT INTO `" + tablePrefix + "_term_relationships` (`object_id`, `term_taxonomy_id`, `term_order`) VALUES (?, ?, 0)";

		Connection conn = DBUtil.getConnection(url, username, passwd);
		PreparedStatement stmt = null;
		PreparedStatement stmtCat = null;
		PreparedStatement stmtTagTaxU = null;
		PreparedStatement stmtTag = null;
		PreparedStatement stmtTagTax = null;
		int num = 0;
		try {
			if (parArrayCat.size() != parArray.size())
				throw new RuntimeException("sorry, 乱了，文章数必须和文章目录关联数相同！");
			conn.setAutoCommit(false); // 开启事务
			stmt = conn.prepareStatement(insert);
			stmtCat = conn.prepareStatement(insertCat);
			num = DBUtil.batchUpdate(parArray, stmt);
			num += DBUtil.batchUpdate(parArrayCat, stmtCat);

			try {

				/* 关联文章标签，标签的特殊在于已存在的标签需要关联到新文章，并且要更新对应标签的关联数count，
				 * 而不存在的标签需要创建。最关键的在于批量处理的文章可能拥有相同的标签，这样就不能放在一个批处理里面。
				 * 需要遍历每个文章，按文章逐个处理标签。
				 */
				// if 存在标签，则更新
				String queryTag = "select term_id, name,slug from " + tablePrefix + "_terms where name=?";
				String updateTagTerm = "update " + tablePrefix + "_term_taxonomy set count=count+1 where term_id=?";
				// else 不存在标签，则创建新标签
				String insertTagTerm = "insert into " + tablePrefix + "_terms(term_id, name, slug) values (?,?,?)";
				String insertTagTaxonomy = "insert into " + tablePrefix + "_term_taxonomy(term_id, taxonomy, description, count) values (?, 'post_tag', '', 1)";
				// 最后关联文章和标签
				String insertTagRelPost = insertCat;

				List<Object[]> insertTag = new ArrayList<Object[]>(); // Term新增数据
				List<Object[]> insertTagTax = new ArrayList<Object[]>(); // 类别新增数据
				List<Object[]> insertTagRel = new ArrayList<Object[]>(); // 标签关联新增数据
				List<Object[]> updateTagTax = new ArrayList<Object[]>(); // 关联数需更新的term

				String queryTermId = "select max(term_id) maxid from " + tablePrefix + "_terms";
				List<Map<String, Object>> list = DBUtil.querySql(queryTermId, null, url, username, passwd);
				int curTermMaxId = Integer.parseInt(list.get(0).get("maxid").toString());

				Map<String, Integer> newTags = new HashMap<String, Integer>(); // 标记新标签，处理新增的帖子之间重复的标签 

				// 批量处理标签
				for (int x = 0; x < parArrayTag.size(); x++) { // 遍历所有帖子的标签
					Object[][] aPostTagArr = parArrayTag.get(x); // 当前帖子的标签
					for (int y = 0; y < aPostTagArr.length; y++) { // 遍历当前帖子的标签
						Object[] aTag = aPostTagArr[y]; // 当前标签

						// 先查询是否已存在该标签
						List<Map<String, Object>> hasTerm = DBUtil.querySql(queryTag, new Object[] { aTag[0] }, url, username, passwd);
						int termId = 0;
						if (newTags.containsKey(aTag[0])) {// 前面帖子已存在的标签，直接复用其term_id。
							termId = newTags.get(aTag[0]);
							updateTagTax.add(new Object[] { termId });
						}
						else if (hasTerm.isEmpty()) { // 没有此标签，创建一个
							termId = ++curTermMaxId;
							insertTag.add(new Object[] { termId, aTag[0], aTag[1] });
							insertTagTax.add(new Object[] { termId });
							newTags.put("name", termId);
						}
						else { // 已经有了，直接关联帖子
							Map<String, Object> curTag = hasTerm.get(0);
							termId = Integer.parseInt(ParseString.nvl(curTag.get("term_id")));

							updateTagTax.add(new Object[] { termId });
						}

						// 关联帖子数据
						insertTagRel.add(new Object[] { aTag[2], termId });
					}

				}

				if (insertTag.size() > 0) {// 此句必须先执行
					stmtTag = conn.prepareStatement(insertTagTerm);
					stmtTagTax = conn.prepareStatement(insertTagTaxonomy);

					DBUtil.batchUpdate(insertTag, stmtTag);
					DBUtil.batchUpdate(insertTagTax, stmtTagTax);
				}

				if (updateTagTax.size() > 0) {
					stmtTagTaxU = conn.prepareStatement(updateTagTerm);
					DBUtil.batchUpdate(updateTagTax, stmtTagTaxU);
				}

				if (insertTagRel.size() > 0) {
					stmtCat = conn.prepareStatement(insertTagRelPost);
					DBUtil.batchUpdate(insertTagRel, stmtCat);
				}

			}
			catch (Exception e) {
				log.error("创建标签异常：");
				e.printStackTrace();
			}

			conn.commit();
		}
		catch (Exception e) {
			conn.rollback();
			throw e;
		}
		finally {
			stmt.close();
			stmtCat.close();
			conn.close();
			count = 0; // // 操作完毕记得计数器清零 否则第二次请求会在此基础上计数
			artlist = null; // 释放内存
			log.info("成功入库……");
		}

		return num;
	}

	// 查询帖子最大id，关联分类目录
	private static final int queryMaxId(String url, String username, String passwd, String dbPre) {
		String sql = "select max(id) maxid from " + dbPre + "_posts";

		List<Map<String, Object>> list = DBUtil.querySql(sql, null, url, username, passwd);

		int curMaxId = Integer.parseInt(list.get(0).get("maxid").toString());

		return curMaxId;
	}
}
